Global Internet Business Solutions ~ GIBS

nopCommerce - Deleting Guest Accounts

Webmaster — Sun, 12 Jan 2025 10:01:00 GMT

Deleting guest account from Customer table: SQL Query DELETE Customer FROM Customer LEFT OUTER JOIN [Order] ON Customer.Id = [Order].CustomerId WHERE ([Order].Id IS NULL) AND (Customer.Email IS NULL) delete guest account - nopCommerce

Getting Eastern Standard Time from Azure SQL

Tue, 03 Nov 2020 16:20:00 GMT

Declare @todays_date Date set @todays_date = CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS date) For example, if you want the current date and time as a datetime value like GETDATE() does, do this: CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime) If you want the current date and time as a datetime2 value like SYSDATETIME() does, do this: CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime2) And if you just want the current date, you can do this: CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS date)

Displaying a Checkbox for Boolean Value in an SSRS Report

Wed, 06 Feb 2019 13:27:00 GMT

Set font to Wingdings . . . . Expression: =IIF(Fields!Parishioner.Value,chr(254),chr(168))

DNN Region Lookup

Tue, 16 Feb 2016 11:20:00 GMT

(SELECT [Value] FROM Lists where EntryID = (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Region' AND PortalID = @PortalID))) AS [State]

SQL Server Reporting Services Permissions Error

Wed, 24 Dec 2014 11:33:00 GMT

To get this resolved you need to start you Internet Explorer as Administrator -> Run as Administrator then open the url http:///Reports this will take you to the "SQL Server Reporting Services" Home page Click on the "Folder Settings" button. Click on the "New Role Assignment" button. Add the Group or user name: DOMAIN\USER_NAME, and permissions you have logged in as. Click on Ok button. This should resolve the issue.

Pivot Query for Ventrian Property Agent

Mon, 15 Dec 2014 13:22:00 GMT

I had a need for getting a property details for a SSR report and came up with the following store procedure . . . I thought it might be useful to someone. /****** Object: StoredProcedure [dbo].[GIBS_Rentals_Get_PropertyDetails] Script Date: 12/15/2014 06:52:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GIBS_Rentals_Get_PropertyDetails] ( @PropertyID int ) AS BEGIN SET NOCOUNT ON; DECLARE @cols AS VARCHAR(MAX), @query AS VARCHAR(MAX), @PropertyIDVarchar AS VARCHAR(10) Set @PropertyIDVarchar = Convert(VARCHAR(10),@PropertyID) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name) FROM Ventrian_PropertyAgent_CustomField FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)') ,1,1,'') --print @cols set @query = 'SELECT PropertyID,' + @cols + ' from ( SELECT PropertyID , cast(Ventrian_PropertyAgent_PropertyValue.CustomValue as Varchar(50)) AS CustomValue, Ventrian_PropertyAgent_CustomField.Name AS [Name] FROM Ventrian_PropertyAgent_PropertyValue INNER JOIN Ventrian_PropertyAgent_CustomField ON Ventrian_PropertyAgent_PropertyValue.CustomFieldID = Ventrian_PropertyAgent_CustomField.CustomFieldID WHERE Ventrian_PropertyAgent_PropertyValue.PropertyID = ' + @PropertyIDVarchar + ' ) x pivot ( min(CustomValue) for Name in (' + @cols + ') ) p ' --print @query execute(@query) END

Get a List of all Databases on a SQL Server Installation

Fri, 24 Oct 2014 09:57:00 GMT

Get a list of the database names and database size in a sql server instance using tsql: EXEC sp_databases

SQL Timeout on Custom DNN module

Wed, 08 Oct 2014 19:33:00 GMT

Try running . . . . exec sp_updatestats;

COALESCE (Transact-SQL)

Mon, 12 May 2014 18:11:00 GMT

COALESCE (Transact-SQL)

How to Shrink Your DotNetNuke Database Log File / Truncate Transaction Log

Sat, 31 Dec 2011 13:05:00 GMT

PROCEDURE: This article tells you how to shrink a DotNetNuke database’s log file via Microsoft’s SQL Server Management Studio. 1) Log into your Management Studio. 2) Right click on the database 3) Select “Properties” 4) Click on “Options” 5) Change the recovery model to “Simple” 6) Click “OK” 7) Right click on the database 8) Select “Tasks” then “Shrink” and finally “Database” 9) Click “OK” 10) Right click on the database 11) Select “Properties” 12) Click on “Options” 13) Change the recovery model to “Full” 14) You can now restart your website.

SQL Linked Server Error

Fri, 07 Oct 2011 11:01:00 GMT

http://support.microsoft.com/kb/839279

Sample Connection Strings

Tue, 09 Aug 2011 09:12:00 GMT

Oracle: "Provider=MSDAORA; Data Source=ORACLE8i7;Persist Security Info=False;Integrated Security=yes" Microsoft Access: "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\bin\LocalAccess40.mdb" SQL Server (Integrated Security): "Provider=SQLOLEDB; Data Source=MySQLServer; Integrated Security=SSPI" SQL Server: "Provider=SQLOLEDB; Data Source=myServerAddress; Initial Catalog=myDataBase; User Id=myUsername; Password=myPassword" MySQL: "Provider=MySQLProv; Server=myServerAddress; Database=myDataBase; Uid=myUsername; Pwd=myPassword;" IBM AS/400: "Provider=SNAOLEDB; DataSource=myServerAddress; UserID=myUsername; Password=myPassword; DataCompression=True;"

DNN User Login report SQL script for DNN Reports Module

Sun, 10 Apr 2011 17:27:00 GMT

Here's a great sql script to use for the DotNetNuke Reports Module: Select -- AU.Username, U.FirstName, U.LastName, AM.loweredEmail as Email, switchoffset(CAST(AM.LastLoginDate as datetimeoffset),'-04:00') as LastLogin from aspnet_Membership as AM with (nolock) join aspnet_Users as AU with (nolock) on AM.UserID = AU.UserID join Users as U with (nolock) on U.UserName = AU.Username where AM.ApplicationID = (select ApplicationID from dbo.aspnet_Applications where ApplicationName = 'DotNetNuke') order by LastLoginDate desc

Finding the Port of MS SQL Named Instance

Thu, 17 Mar 2011 10:12:00 GMT

When running a named instance of MS SQL the instance will run on a port other than the usual port 1433. To determine the port you can use the following query: DECLARE @tcp_port nvarchar(5) EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL\MSSQLSERVER\SUPERSOCKETNETLIB\TCP', @value_name = 'TcpPort', @value = @tcp_port OUTPUT select @tcp_port In my case the instance was named "MSSQL".

Farming Leads Import

Sun, 09 Jan 2011 12:26:00 GMT

INSERT [dbo].[Farming] ( [First Name] [Last Name] Title [Mailing Address] [Mailing City] [Mailing Province/State] [Mailing Postal/ZIP] WebAddress Phone1 Fax Address City [Province/State] [Postal/ZIP] Company, , , , , , , , , , , , , , ) SELECT Company [First Name] [Last Name] Title [Mailing Address] [Mailing City] [Mailing Province/State] [Mailing Postal/ZIP] + ' ' + COALESCE(AKA,'') as Company, , , , , , , , WebAddress , Phone1 Fax [Mailing Address] [Mailing City] , , as Address, as City, [Mailing Province/State] [Mailing Postal/ZIP] AS [Province/State], AS [Postal/ZIP] FROM Farming2011Data

Create a New Database From an Existing Database Backup (SQL Server Management Studio)

Mon, 20 Dec 2010 12:52:00 GMT

This topic explains how to create a new database from an existing full database backup. Security Note We recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database. Important Under the full or bulk-logged recovery model, before you can restore a database in SQL Server Management Studio, you must back up the active transaction log (known as the tail of the log). For more information, see How to: Back Up a Transaction Log (SQL Server Management Studio). To create a new database from an existing full backup After you connect to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree. Right-click Databases, point to Restore, and then click Database. Clicking Database opens the Restore Database dialog box. On the General page, the name of the source database appears in the To database list by default. To create the new database, enter its name in the list box. In the To a point in time text box, either use the default (Most recent possible) or click the browse button to select a specific date and time. To specify the source and location of the backup sets to restore, click one of the following options: From database Enter a database name in the list box. From device Click the browse button. This opens the Specify Backup dialog box. In the Backup media list, select one of the listed device types. To select one or more devices for the Backup location list, click Add. After you add the device or devices you want to the Backup location list, click OK to return to the General page. In the Select the backup sets to restore grid, select the backups to restore. This grid displays the backups available for the specified location. By default, a recovery plan is suggested. To override the suggested recovery plan, you can change the selections in the grid. Backups that depend on the restoration of an earlier backup are automatically deselected when the earlier backup is deselected. For information about the columns in the Select the backup sets to restore grid, see Restore Database (General Page). To view or select the advanced options, click Options in the Select a page pane. In the Restore options panel, optionally select any of the options: Overwrite the existing database Preserve the replication settings Prompt before restoring each backup Restrict access to the restored database For complete descriptions of theses options of the Restore options panel, see Restore Database (Options Page). You can move the database that you are restoring by specifying new restore destinations for the files in the Restore the database files as grid. The grid lists the original full path for each data or log file of the database and the restore destination for each file. The following table describes the columns of the Restore the database files as grid. Header Values Original File Name The full path of a data file or log file of the original database. Restore As The path and file name that will be used as the restore destination for this data file or log file. To move the database that you are restoring to a different location, edit the Restore As field of each file and specify the new destination path or file name, or both. Also, if you want to restore a copy of a database on the server instance while the original database is still attached, you must specify a new database name in the To database field on the General page of the Restore Database dialog box. Note Changing the restore destinations in the Restore As column is equivalent to using the MOVE option in RESTORE statements. The Recovery state panel determines the state of the database after the restore operation. The default behavior is to: Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY) Note Choose this option only if you are restoring all the necessary backups now. Alternatively, you can select to either: Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY) Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY) For descriptions of the options of the Restore options panel, see Restore Database (Options Page).

SQL SPLIT Function

Fri, 30 Apr 2010 09:15:00 GMT

Add the helper SPLIT function to your database CREATE FUNCTION dbo.Split ( @List nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1, 1), Value nvarchar(100) ) AS BEGIN While (Charindex(@SplitOn, @List)>0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List)-1))) Set @List = Substring(@List, Charindex(@SplitOn, @List)+len(@SplitOn), len(@List)) End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) Return END

SEO Content Pages

Fri, 12 Mar 2010 13:31:00 GMT

PROCEDURE GIBS_SEO_Content nvarchar(50) = null AS BEGIN if (len(@city)=0) begin set @city=null end SET NOCOUNT ON; SELECT City, Village , REPLACE (City,' ','-') + '/' + REPLACE (Village,' ','-') AS EncodeVillageFROM Town_to_VillageWHERE (Village = coalesce(@City,'Harwich') ) END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO @City CREATE